﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessInstockDetailed
// 文件功能描述：零星物品出库数据访问层
//
// 
// 创建标识：2012-01-14 zhou
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessOutStockSundries
    {        
        /// <summary>
        /// 保存零星物品出库
        /// </summary>
        /// <param name="igpara"></param>
        /// <returns></returns>
        public int SaveOutstockGroup(object[] igpara)
        {
            StringBuilder str = new StringBuilder();
            str.Append("insert into Con_OutStock_Detailed_Sporadic(LeanDt,GoodsID,LeanDept,LeanCount,Operator_ID,RecordDateTime,OrderNo) ");
            str.Append("values(@LeanDt,@GoodsID,@LeanDept,@LeanCount,@Operator_ID,@RecordDateTime,@OrderNo)"); 
            SqlParameter[] sqlpara = new SqlParameter[]{
                new SqlParameter("@LeanDt",igpara[0]),
                new SqlParameter("@GoodsID",igpara[1]),
                new SqlParameter("@LeanDept",igpara[2]),
                new SqlParameter("@LeanCount",igpara[3]),
                new SqlParameter("@Operator_ID",igpara[4]),
                new SqlParameter("@RecordDateTime",igpara[5]),
                new SqlParameter("@OrderNo",igpara[6])
            };           
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, str.ToString(), sqlpara);
        }


        /// <summary>
        /// 更新零星物品出库
        /// </summary>
        /// <param name="igpara"></param>
        /// <returns></returns>
        public int UpdateOutstockGroup(object[] igpara)
        {
            StringBuilder str = new StringBuilder();
            str.Append("update Con_OutStock_Detailed_Sporadic set ");
            str.Append("LeanDt=@LeanDt,GoodsID=@GoodsID,LeanDept=@LeanDept,LeanCount=@LeanCount,Operator_ID=@Operator_ID,RecordDateTime=@RecordDateTime ");           
            str.Append("where ID = @ID");
            SqlParameter[] sqlpara = new SqlParameter[]{
                new SqlParameter("@ID",igpara[0]),
                new SqlParameter("@LeanDt",igpara[1]),
                new SqlParameter("@GoodsID",igpara[2]),
                new SqlParameter("@LeanDept",igpara[3]),
                new SqlParameter("@LeanCount",igpara[4]),
                new SqlParameter("@Operator_ID",igpara[5]),
                new SqlParameter("@RecordDateTime",igpara[6])
            };
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, str.ToString(), sqlpara);
        }


        /// <summary>
        /// 获取一条零星物品出库信息
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns>食材出库信息</returns>
        public DataSet GetOutstockGroup(int ID)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select ID,LeanDt,GoodsID,LeanDept,LeanCount,Operator_ID,RecordDateTime,OrderNo ");
            str.Append("from Con_OutStock_Detailed_Sporadic where ID = @ID");
            SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@ID",ID)
            };
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString(), para);
        }


        /// <summary>
        /// 食材类型数量合计
        /// </summary>
        /// <returns></returns>
        public int GetOutstockGroupCount(string Condition)
        {
            sqldb db = new sqldb();
            string query = "select count(1) from Con_OutStock_Detailed_Sporadic where 1=1 " + Condition; 
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }

        public int GetSportadicOutCount(string Condition)
        {
            sqldb db = new sqldb();
            string query = "select count(1) from v_LingxingWp where 1=1 " + Condition;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 食材类型数量合计(报表）
        /// </summary>
        /// <returns></returns>
        public int GetOutstockGroupCountBb(string Condition)
        {
            sqldb db = new sqldb();
            string query = "select count(1) from Con_Goods where 1=1 " + Condition;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 获取零星物品出库信息（分页）
        /// </summary>
        /// <param name="pagesize">页大小</param>
        /// <param name="startindex">起始页</param>
        /// <param name="dict">查询条件</param>
        /// <returns>食材入库信息</returns>
        public DataSet GetOutstockGroup(int pagesize, int startindex,string condition)
        {
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            StringBuilder str = new StringBuilder();
            str.Append("select a.ID,a.LeanDt,a.GoodsID,a.LeanDept,a.LeanCount,a.Operator_ID,a.RecordDateTime,OrderNo from (");
            str.Append("select ROW_NUMBER() OVER(ORDER BY ID DESC) rownumber,ID,LeanDt,GoodsID,LeanDept,LeanCount,Operator_ID,RecordDateTime,OrderNo ");
            str.Append("from Con_OutStock_Detailed_Sporadic where 1=1 ");
            str.Append(condition);
            str.Append(") a ");
            str.Append("where a.rownumber > ");
            str.Append(startrow);
            str.Append(" and a.rownumber < ");
            str.Append(endrow);
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString());
            return ds;
        }

        /// <summary>
        /// 获取零星物品出库信息（不分页）
        /// </summary>
        /// <param name="dict">查询条件</param>
        /// <returns>食材入库信息</returns>
        public DataSet GetOutstockGroup(string condition)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select A.ID,A.LeanDt,A.GoodsID,A.LeanDept,A.LeanCount,A.Operator_ID,A.RecordDateTime from (");
            str.Append("select ROW_NUMBER() OVER(ORDER BY ID DESC) rownumber,ID,LeanDt,GoodsID,LeanDept,LeanCount,Operator_ID,RecordDateTime ");
            str.Append("from Con_OutStock_Detailed_Sporadic where 1=1 ");
            str.Append(condition);
            str.Append(") A ");
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString());
            return ds;
        }

        /// <summary>
        /// 删除食材出库
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns></returns>
        public int DeleteOutstockGroup(int ID)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from Con_OutStock_Detailed_Sporadic where ID = @ID");
            SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@ID",ID)
            };
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, str.ToString(), para);
        }
        /// <summary>
        /// 生成零星物品出库批号
        /// </summary>
        /// <returns></returns>
        public string GetOutstockSundiresOrderNo()
        {
            sqldb db = new sqldb();
            string query = "Execute GetCon_Outstock_Detailed_Sporadic_OrderNo";
            object strorderno = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return strorderno.ToString();
        }

        /// <summary>
        /// 食堂零星物品领用表       
        /// </summary>
        /// <returns></returns>
        public DataSet BuilderReportLy(int yy,int mm,string st,int pagesize, int startindex)
        {
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            sqldb db = new sqldb();
            string query = "Execute Proc_OutStockSporadicNew " + yy.ToString() + ","
                        + mm.ToString() + "," + st + "," + startrow.ToString() + "," + endrow.ToString();
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }

        /// <summary>
        /// 验证库存数量
        /// </summary>
        /// <param name="GID"></param>
        /// <returns></returns>
        public bool OutStockYzh(int GID, decimal Count)
        {
            sqldb db = new sqldb();
            string query = "Execute Proc_OutStockYzh " + GID.ToString();
            object stockcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToDecimal(stockcount) >= Count ? true : false;
        }
    }
}
